--exec sp_load_assembly_release_lot_bin_info 27143, 1332, 'bin1&bin2',0
--exec sp_load_assembly_release_lot_bin_info 13360, 868, 'Bin1', -1


ALTER  PROCEDURE sp_load_assembly_release_lot_bin_info
(
@LOT_ID INT,
@ASSM_PROCESS_STEP_ID INT,
@SELECTED_BINS VARCHAR(200),
@ASSM_RELEASE_ID INT
)
AS
BEGIN
    DECLARE @SELECTED_BINS_COPY VARCHAR(200)
    DECLARE @BINS_SUM_STRING VARCHAR(200)
    DECLARE @CURRENT_BIN VARCHAR(200)
    DECLARE @DELIM_CHAR CHAR(1)
    DECLARE @DELIM_IDX INT
    DECLARE @LOT_NUMBER varchar(50)
    DECLARE @SHIPPED_LOT_NUMBER varchar(50)
	DECLARE @FAB_LOT_NUMBER varchar(50)
    DECLARE @PARENT_PRODUCT_ID INT
    DECLARE @QUERYSTRING NVARCHAR(1000)
    DECLARE @PARAMDEFINITION NVARCHAR(100)
    DECLARE @WAFER_ID INT
    DECLARE @BAL_QTY_FOR_WAFER INT
    DECLARE @IS_DOWN_GRADE_BIN BIT
    DECLARE @CURRENT_BIN_QTY INT
    DECLARE @DOWNGRADE_BIN_INFO_CURSOR CURSOR
    DECLARE @BIN_FOR_DOWNGRADED_BIN VARCHAR(5)
    DECLARE @BIN_INFO_FOR_CURRENT_WAFER VARCHAR(100)
    DECLARE @QTY_IN_ASSM_LOT_TABLE_FOR_CURRENT_WAFER INT
    DECLARE @IS_WAFER_SELECTED BIT
    DECLARE @AVAILABLE_QTY_THAT_CAN_BE_PULLED INT
    DECLARE @QTY_TO_BUILD INT
    DECLARE @RELEASE_STATUS VARCHAR(50)
    DECLARE @IS_RELEASE_CANCELLED BIT
    DECLARE @DIE_BANK_PART_NUMBER VARCHAR(50)
	DECLARE @DIE_PER_PACKAGES INT
	DECLARE @TEST_RELEASE_LOT_ID INT		-- AUTO GENERATED ID IN TEST RELEASE LOT TABLE

	DECLARE @TRANSIT_LOT_COUNT INT
	DECLARE @IS_PARTIAL_RECEIVE_LOT INT
	
	DECLARE @TOTAL_WAFER_COUNT		INT
	DECLARE @RELEASE_WAFER_COUNT	INT
	DECLARE @AVAILABLE_WAFER_COUNT	INT

	SET @IS_PARTIAL_RECEIVE_LOT = 1
    SET @DELIM_CHAR = '&'
    SET @SELECTED_BINS_COPY = @SELECTED_BINS
    SET @IS_RELEASE_CANCELLED = 0

    SELECT @DIE_PER_PACKAGES = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Dies/Package' AND  PROCESSSTEP_ID = @ASSM_PROCESS_STEP_ID
	IF( @DIE_PER_PACKAGES IS NULL )
	BEGIN
		SET @DIE_PER_PACKAGES = 1
	END

    DECLARE @RESULTS TABLE ( IS_SELECTED INT, LOT_ID INT, LOT_NUMBER VARCHAR(50), WAFER_ID INT, BIN_INFO VARCHAR(100), AVAILABLE_QTY INT, QTY_PULLED INT, QTY_TO_BUILD INT, BIN_INFO_INCLUDING_DOWN_BIN VARCHAR(100), PART_NUMBER VARCHAR(50), TRLT_ID INT, SHIPPED_LOT_NUMBER VARCHAR(50), FAB_LOT_NUMBER VARCHAR(50), IS_PARTIAL_RECEIVE_LOT INT, TOTAL_WAFER_COUNT INT )

    SELECT @PARENT_PRODUCT_ID = P_PARENT_PRODUCT_ID FROM PRODUCT WHERE PROCESSSTEP_ID = @ASSM_PROCESS_STEP_ID

    SET @BINS_SUM_STRING = REPLACE( REPLACE(@SELECTED_BINS, 'Bin', 'Bin_')+'_BALANCE_QTY', @DELIM_CHAR, '_BALANCE_QTY+')
    SET @BINS_SUM_STRING = REPLACE( @BINS_SUM_STRING, 'Bin', ' ISNULL ( Bin' ) 
    SET @BINS_SUM_STRING = REPLACE( @BINS_SUM_STRING, 'QTY', 'QTY, 0 ) ' ) 

    SELECT @LOT_NUMBER = LOT_NUMBER, @FAB_LOT_NUMBER = FAB_LOT_NUMBER  FROM LOT_TABLE WHERE LOT_ID = @LOT_ID
    SELECT @RELEASE_STATUS = STATUS FROM ASSM_RELEASE_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSM_RELEASE_ID
    IF( @RELEASE_STATUS = 'CANCELLED')
    BEGIN
        SET @IS_RELEASE_CANCELLED = 1
    END
	
	SELECT @RELEASE_WAFER_COUNT = COUNT(*) FROM ASSM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSM_RELEASE_ID AND LOT_ID = @LOT_ID
	SELECT @AVAILABLE_WAFER_COUNT = COUNT(*) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @LOT_ID AND BIN_1_BALANCE_QTY > 0 AND 
	WAFER_ID NOT IN (SELECT DISTINCT WAFER_ID FROM ASSM_RELEASE_TABLE ART, ASSM_RELEASE_LOT_TABLE ARLT 
	WHERE ART.ASSEMBLY_RELEASE_ID = ARLT.ASSEMBLY_RELEASE_ID AND ARLT.LOT_ID = @LOT_ID AND ART.STATUS <> 'CANCELLED' )
	
	SET @TOTAL_WAFER_COUNT = @RELEASE_WAFER_COUNT + @AVAILABLE_WAFER_COUNT
	
    SET @QUERYSTRING = N'DECLARE WAFERS_CURSOR CURSOR FOR SELECT WAFER_ID, '+@BINS_SUM_STRING+', IS_DOWN_GRADE_BIN FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @LOT_ID ORDER BY CAST(WAFER_ID AS INT)'
    SET @PARAMDEFINITION = N'@LOT_ID INT'
    EXECUTE SP_EXECUTESQL @QUERYSTRING, @PARAMDEFINITION, @LOT_ID = @LOT_ID
    OPEN WAFERS_CURSOR
    FETCH FROM WAFERS_CURSOR INTO @WAFER_ID, @BAL_QTY_FOR_WAFER, @IS_DOWN_GRADE_BIN
    WHILE @@FETCH_STATUS = 0
    BEGIN
        --print 'wafer id : ' + convert (varchar, @WAFER_ID)
        --print '@BAL_QTY_FOR_WAFER : ' + convert (varchar, @BAL_QTY_FOR_WAFER)
        --print 'IS_DOWN_GRADE_BIN : ' + convert ( varchar, @IS_DOWN_GRADE_BIN)
        SET @BIN_INFO_FOR_CURRENT_WAFER = ''
        SET @IS_WAFER_SELECTED = 1
        SET @AVAILABLE_QTY_THAT_CAN_BE_PULLED = 0
		SET @TEST_RELEASE_LOT_ID = -1

        IF ( @ASSM_RELEASE_ID < 1 )
        BEGIN
            SET @SELECTED_BINS = @SELECTED_BINS + @DELIM_CHAR
            --print '1'
            WHILE( CHARINDEX( @DELIM_CHAR, @SELECTED_BINS, 1 ) <> 0 )
            BEGIN
                SET @DELIM_IDX = CHARINDEX( @DELIM_CHAR, @SELECTED_BINS, 1 )
                SET @CURRENT_BIN = SUBSTRING( @SELECTED_BINS, 0, @DELIM_IDX )
                SET @QUERYSTRING = N'DECLARE CURRENT_BIN_QTY_CURSOR CURSOR FOR SELECT ' + REPLACE(@CURRENT_BIN, 'Bin', 'Bin_')+'_BALANCE_QTY' + ' FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID'
                SET @PARAMDEFINITION = N'@LOT_ID INT, @WAFER_ID INT'
                --print '2'
                EXECUTE SP_EXECUTESQL @QUERYSTRING, @PARAMDEFINITION, @LOT_ID = @LOT_ID, @WAFER_ID = @WAFER_ID
                OPEN CURRENT_BIN_QTY_CURSOR
                FETCH NEXT FROM CURRENT_BIN_QTY_CURSOR INTO @CURRENT_BIN_QTY
                IF( @CURRENT_BIN_QTY > 0 )
                BEGIN
                    IF( @IS_DOWN_GRADE_BIN = 0 )
                    BEGIN
                        --print '@CURRENT_BIN : ' + convert(varchar, @CURRENT_BIN )
                        --print '@PARENT_PRODUCT_ID : ' + convert( varchar, @PARENT_PRODUCT_ID )
                        SET @DOWNGRADE_BIN_INFO_CURSOR = CURSOR FOR SELECT BIN_NUMBER FROM MRP_BINNING_MAPPING WHERE BASE_PRODUCT_ID = @PARENT_PRODUCT_ID AND DOWNSIZE_BIN_NUMBER = @CURRENT_BIN
                        OPEN @DOWNGRADE_BIN_INFO_CURSOR
                        FETCH NEXT FROM @DOWNGRADE_BIN_INFO_CURSOR INTO @BIN_FOR_DOWNGRADED_BIN
                        WHILE @@FETCH_STATUS = 0
                        BEGIN
                            --print '@BIN_FOR_DOWNGRADED_BIN : ' + @BIN_FOR_DOWNGRADED_BIN
                            IF ( @BIN_FOR_DOWNGRADED_BIN IS NOT NULL AND LEN(@BIN_FOR_DOWNGRADED_BIN) > 0 )
                            BEGIN
                                SET @BIN_INFO_FOR_CURRENT_WAFER = @BIN_INFO_FOR_CURRENT_WAFER + SUBSTRING(@BIN_FOR_DOWNGRADED_BIN, 4, LEN(@BIN_FOR_DOWNGRADED_BIN)) + @DELIM_CHAR
                                --print '@BIN_INFO_FOR_CURRENT_WAFER : ' + @BIN_INFO_FOR_CURRENT_WAFER
                            END
                            FETCH NEXT FROM @DOWNGRADE_BIN_INFO_CURSOR INTO @BIN_FOR_DOWNGRADED_BIN
                        END
                        CLOSE @DOWNGRADE_BIN_INFO_CURSOR
			            DEALLOCATE @DOWNGRADE_BIN_INFO_CURSOR
                        SET @BIN_INFO_FOR_CURRENT_WAFER = @BIN_INFO_FOR_CURRENT_WAFER + SUBSTRING(@CURRENT_BIN, 4, LEN(@CURRENT_BIN)) + @DELIM_CHAR
                        --print '@BIN_INFO_FOR_CURRENT_WAFER : ' + @BIN_INFO_FOR_CURRENT_WAFER
                    END
                    ELSE
                    BEGIN
                        SET @BIN_INFO_FOR_CURRENT_WAFER = @BIN_INFO_FOR_CURRENT_WAFER +  SUBSTRING(@CURRENT_BIN, 4, LEN(@CURRENT_BIN)) + @DELIM_CHAR
                    END
                END
                CLOSE CURRENT_BIN_QTY_CURSOR
                DEALLOCATE CURRENT_BIN_QTY_CURSOR
                SET @SELECTED_BINS = SUBSTRING( @SELECTED_BINS, @DELIM_IDX+1, LEN( @SELECTED_BINS ))
                --print 'selected bins : ' + @SELECTED_BINS
                --print CHARINDEX( @DELIM_CHAR, @SELECTED_BINS, 1 )
            END
            --print '@BIN_INFO_FOR_CURRENT_WAFER : ' + @BIN_INFO_FOR_CURRENT_WAFER
            SET @BIN_INFO_FOR_CURRENT_WAFER = 'Bin'+SUBSTRING(@BIN_INFO_FOR_CURRENT_WAFER, 0, LEN(@BIN_INFO_FOR_CURRENT_WAFER))
            SET @AVAILABLE_QTY_THAT_CAN_BE_PULLED = @BAL_QTY_FOR_WAFER
        END
        ELSE
        BEGIN
			IF ( (SELECT COUNT(*) FROM ASSM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSM_RELEASE_ID AND WAFER_ID = 0) > 0 ) --RELEASE WITH WAFER ID 0, ADDED FOR LEGACY RELEASES
			BEGIN
					SELECT @AVAILABLE_QTY_THAT_CAN_BE_PULLED = QTY_PULLED, @QTY_TO_BUILD = QTY_TO_BUILD, @BIN_INFO_FOR_CURRENT_WAFER = BIN_INFO, @TEST_RELEASE_LOT_ID = TRLT_ID, @SHIPPED_LOT_NUMBER = SHIPPED_LOT_NUMBER
						FROM ASSM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSM_RELEASE_ID AND LOT_ID = @LOT_ID AND WAFER_ID = 0
					SET @WAFER_ID = 0
					SET @BAL_QTY_FOR_WAFER = @AVAILABLE_QTY_THAT_CAN_BE_PULLED
					SET @IS_WAFER_SELECTED = 0
			END
			ELSE IF ( ( SELECT COUNT(*) FROM ASSM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSM_RELEASE_ID AND LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID ) > 0 )
            BEGIN
                SELECT @AVAILABLE_QTY_THAT_CAN_BE_PULLED = QTY_PULLED, @QTY_TO_BUILD = QTY_TO_BUILD, @BIN_INFO_FOR_CURRENT_WAFER = BIN_INFO, @TEST_RELEASE_LOT_ID = TRLT_ID, @SHIPPED_LOT_NUMBER = SHIPPED_LOT_NUMBER
					FROM ASSM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSM_RELEASE_ID AND LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID

				SELECT @TRANSIT_LOT_COUNT = COUNT(*) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, PRODUCT P 
					WHERE LT.LOT_ID = SL.LOT_ID  AND SL.STATUS = 'Close' AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
					AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PS_TYPE = 'Die Bank' AND SL.SL_NEXT_PROCESS <> 'Die Bank'
					AND LT.LOT_NUMBER = @SHIPPED_LOT_NUMBER

				IF @TRANSIT_LOT_COUNT > 0
				BEGIN
					SET @IS_PARTIAL_RECEIVE_LOT = 0
				END
				ELSE
				BEGIN
					SET @IS_PARTIAL_RECEIVE_LOT = 1
				END

                IF ( @IS_RELEASE_CANCELLED = 0 )
                BEGIN
                    SET @BAL_QTY_FOR_WAFER = @BAL_QTY_FOR_WAFER + @AVAILABLE_QTY_THAT_CAN_BE_PULLED
                END
                SET @IS_WAFER_SELECTED = 0
            END
            ELSE
            BEGIN
                SET @SELECTED_BINS = @SELECTED_BINS + @DELIM_CHAR
                --print '1'
                WHILE( CHARINDEX( @DELIM_CHAR, @SELECTED_BINS, 1 ) <> 0 )
                BEGIN
                    SET @DELIM_IDX = CHARINDEX( @DELIM_CHAR, @SELECTED_BINS, 1 )
                    SET @CURRENT_BIN = SUBSTRING( @SELECTED_BINS, 0, @DELIM_IDX )
                    SET @QUERYSTRING = N'DECLARE CURRENT_BIN_QTY_CURSOR CURSOR FOR SELECT ' + REPLACE(@CURRENT_BIN, 'Bin', 'Bin_')+'_BALANCE_QTY' + ' FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID'
                    SET @PARAMDEFINITION = N'@LOT_ID INT, @WAFER_ID INT'
                    --print '2'
                    EXECUTE SP_EXECUTESQL @QUERYSTRING, @PARAMDEFINITION, @LOT_ID = @LOT_ID, @WAFER_ID = @WAFER_ID
                    OPEN CURRENT_BIN_QTY_CURSOR
                    FETCH NEXT FROM CURRENT_BIN_QTY_CURSOR INTO @CURRENT_BIN_QTY
                    IF( @CURRENT_BIN_QTY > 0 )
                    BEGIN
                        IF( @IS_DOWN_GRADE_BIN = 0 )
                        BEGIN
                            --print '@CURRENT_BIN : ' + convert(varchar, @CURRENT_BIN )
                            print '@PARENT_PRODUCT_ID : ' + convert( varchar, @PARENT_PRODUCT_ID )
                            SET @DOWNGRADE_BIN_INFO_CURSOR = CURSOR FOR SELECT BIN_NUMBER FROM MRP_BINNING_MAPPING WHERE BASE_PRODUCT_ID = @PARENT_PRODUCT_ID AND DOWNSIZE_BIN_NUMBER = @CURRENT_BIN
                            OPEN @DOWNGRADE_BIN_INFO_CURSOR
                            FETCH NEXT FROM @DOWNGRADE_BIN_INFO_CURSOR INTO @BIN_FOR_DOWNGRADED_BIN
                            WHILE @@FETCH_STATUS = 0
                            BEGIN
                                --print '@BIN_FOR_DOWNGRADED_BIN : ' + @BIN_FOR_DOWNGRADED_BIN
                                IF ( @BIN_FOR_DOWNGRADED_BIN IS NOT NULL AND LEN(@BIN_FOR_DOWNGRADED_BIN) > 0 )
                                BEGIN
                                    SET @BIN_INFO_FOR_CURRENT_WAFER = @BIN_INFO_FOR_CURRENT_WAFER + SUBSTRING(@BIN_FOR_DOWNGRADED_BIN, 4, LEN(@BIN_FOR_DOWNGRADED_BIN)) + @DELIM_CHAR
                                    --print '@BIN_INFO_FOR_CURRENT_WAFER : ' + @BIN_INFO_FOR_CURRENT_WAFER
                                END
                                FETCH NEXT FROM @DOWNGRADE_BIN_INFO_CURSOR INTO @BIN_FOR_DOWNGRADED_BIN
                            END
                            CLOSE @DOWNGRADE_BIN_INFO_CURSOR
							DEALLOCATE @DOWNGRADE_BIN_INFO_CURSOR
							
                            SET @BIN_INFO_FOR_CURRENT_WAFER = @BIN_INFO_FOR_CURRENT_WAFER + @CURRENT_BIN + @DELIM_CHAR
                            --print '@BIN_INFO_FOR_CURRENT_WAFER : ' + @BIN_INFO_FOR_CURRENT_WAFER

                        END
                        ELSE
                        BEGIN
                            SET @BIN_INFO_FOR_CURRENT_WAFER = @BIN_INFO_FOR_CURRENT_WAFER +  SUBSTRING(@CURRENT_BIN, 4, LEN(@CURRENT_BIN)) + @DELIM_CHAR
                        END
                    END
                    close CURRENT_BIN_QTY_CURSOR
                    deallocate CURRENT_BIN_QTY_CURSOR
                    SET @SELECTED_BINS = SUBSTRING( @SELECTED_BINS, @DELIM_IDX+1, LEN( @SELECTED_BINS ))
                    --print 'selected bins : ' + @SELECTED_BINS
                    --print CHARINDEX( @DELIM_CHAR, @SELECTED_BINS, 1 )
                END
                                --print '@BIN_INFO_FOR_CURRENT_WAFER : ' + @BIN_INFO_FOR_CURRENT_WAFER
                SET @BIN_INFO_FOR_CURRENT_WAFER = 'Bin'+SUBSTRING(@BIN_INFO_FOR_CURRENT_WAFER, 0, LEN(@BIN_INFO_FOR_CURRENT_WAFER))
                SET @AVAILABLE_QTY_THAT_CAN_BE_PULLED = @BAL_QTY_FOR_WAFER
            END
        END
        SET @SELECTED_BINS = @SELECTED_BINS_COPY
        --print '@BAL_QTY_FOR_WAFER : ' + convert(varchar, @BAL_QTY_FOR_WAFER)
        IF( @BAL_QTY_FOR_WAFER > 0 OR @IS_RELEASE_CANCELLED = 1 )
        BEGIN
            SELECT @DIE_BANK_PART_NUMBER = Part_Number FROM LOT_TABLE LT, PRODUCT P, PROCESS_STEP PS WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
            AND PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND LOT_NUMBER = @LOT_NUMBER AND PS_TYPE like 'SORT'

            IF( @SHIPPED_LOT_NUMBER IS NULL )
            BEGIN
                SET @SHIPPED_LOT_NUMBER =  @LOT_NUMBER
            END

            INSERT INTO @RESULTS ( IS_SELECTED, LOT_ID, LOT_NUMBER, WAFER_ID, BIN_INFO, AVAILABLE_QTY, QTY_PULLED, QTY_TO_BUILD, BIN_INFO_INCLUDING_DOWN_BIN, PART_NUMBER, TRLT_ID, SHIPPED_LOT_NUMBER, FAB_LOT_NUMBER, IS_PARTIAL_RECEIVE_LOT, TOTAL_WAFER_COUNT)
            VALUES ( @IS_WAFER_SELECTED, @LOT_ID, @LOT_NUMBER, @WAFER_ID, @SELECTED_BINS_COPY, @BAL_QTY_FOR_WAFER, @AVAILABLE_QTY_THAT_CAN_BE_PULLED, @QTY_TO_BUILD, @BIN_INFO_FOR_CURRENT_WAFER, @DIE_BANK_PART_NUMBER, @TEST_RELEASE_LOT_ID, @SHIPPED_LOT_NUMBER, @FAB_LOT_NUMBER, @IS_PARTIAL_RECEIVE_LOT , @TOTAL_WAFER_COUNT)
        END
	IF ( (SELECT COUNT(*) FROM ASSM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @ASSM_RELEASE_ID AND WAFER_ID = 0) > 0 ) --RELEASE WITH WAFER ID 0, ADDED FOR LEGACY RELEASES
	BEGIN
		BREAK --stopping the loop as we no need to iterate through all wafers
	END

		SET @TEST_RELEASE_LOT_ID = -1
		SET @SHIPPED_LOT_NUMBER =  NULL

        FETCH FROM WAFERS_CURSOR INTO @WAFER_ID, @BAL_QTY_FOR_WAFER, @IS_DOWN_GRADE_BIN
    END
    CLOSE WAFERS_CURSOR
    DEALLOCATE WAFERS_CURSOR

    SELECT * FROM @RESULTS
END